<?php
/**
 * (logic)
 */
namespace App\Logic\FBATransit;

use Illuminate\Support\Facades\DB;
class InitFBATransit
{
    const FROM_DATE = '2021-01-01 00:00:00';

    public static function getTransitByArea($areaId,$pSkuId){
        $date = self::FROM_DATE;
        $shipNum = DB::table('d_fba_delivery_detail as dd')
            ->join('d_fba_delivery as d', 'dd.delivery_no', '=', 'd.delivery_no')
            ->where('d.area_id',$areaId)
            ->where('dd.psku_id',$pSkuId)
            ->where('d.status','>',3)
            ->where('d.status','<>',7)
            ->where('d.created_at','>=',$date)
            ->sum('dd.ship_num');
        $applyNum = DB::table('d_fba_delivery_detail as dd')
            ->join('d_fba_delivery as d', 'dd.delivery_no', '=', 'd.delivery_no')
            ->where('d.area_id',$areaId)
            ->where('dd.psku_id',$pSkuId)
            ->where('d.status','<=',3)
            ->where('d.created_at','>=',$date)
            ->sum('dd.apply_num');
        $receivedNum = DB::table('d_fba_delivery_detail as dd')
            ->join('d_fba_delivery as d', 'dd.delivery_no', '=', 'd.delivery_no')
            ->where('d.area_id',$areaId)
            ->where('dd.psku_id',$pSkuId)
            ->where('d.created_at','>=',$date)
            ->sum('dd.received_num');
        $num = ($shipNum ?: 0) + ($applyNum ?: 0) - ($receivedNum ?: 0);
        $outShipNum = DB::table('os_fba_delivery_detail as odd')
            ->join('os_fba_delivery as od', 'odd.delivery_no', '=', 'od.delivery_no')
            ->where('od.area_id',$areaId)
            ->where('odd.psku_id',$pSkuId)
            ->where('od.status','>',2)
            ->where('od.created_at','>=',$date)
            ->sum('odd.ship_num');
        $outApplyNum = DB::table('os_fba_delivery_detail as odd')
            ->join('os_fba_delivery as od', 'odd.delivery_no', '=', 'od.delivery_no')
            ->where('od.area_id',$areaId)
            ->where('odd.psku_id',$pSkuId)
            ->where('od.status','<=',2)
            ->where('od.created_at','>=',$date)
            ->sum('odd.apply_num');
        $outReceivedNum = DB::table('os_fba_delivery_detail as odd')
            ->join('os_fba_delivery as od', 'odd.delivery_no', '=', 'od.delivery_no')
            ->where('od.area_id',$areaId)
            ->where('odd.psku_id',$pSkuId)
            ->where('od.created_at','>=',$date)
            ->sum('odd.received_num');
        $outNum = ($outShipNum ?: 0) + ($outApplyNum ?: 0) - ($outReceivedNum ?: 0);
        return ($num < 0 ? 0 : $num) + ($outNum < 0 ? 0 : $outNum);
    }

    public static function getTransitByCountry($countryId,$pSkuId){
        $date = self::FROM_DATE;
        $shipNum = DB::table('d_fba_delivery_detail as dd')
            ->join('d_fba_delivery as d', 'dd.delivery_no', '=', 'd.delivery_no')
            ->where('d.country_id',$countryId)
            ->where('dd.psku_id',$pSkuId)
            ->where('d.status','>',3)
            ->where('d.status','<>',7)
            ->where('d.created_at','>=',$date)
            ->sum('dd.ship_num');
        $applyNum = DB::table('d_fba_delivery_detail as dd')
            ->join('d_fba_delivery as d', 'dd.delivery_no', '=', 'd.delivery_no')
            ->where('d.country_id',$countryId)
            ->where('dd.psku_id',$pSkuId)
            ->where('d.status','<=',3)
            ->where('d.created_at','>=',$date)
            ->sum('dd.apply_num');
        $receivedNum = DB::table('d_fba_delivery_detail as dd')
            ->join('d_fba_delivery as d', 'dd.delivery_no', '=', 'd.delivery_no')
            ->where('d.country_id',$countryId)
            ->where('dd.psku_id',$pSkuId)
            ->where('d.created_at','>=',$date)
            ->sum('dd.received_num');
        $num = ($shipNum ?: 0) + ($applyNum ?: 0) - ($receivedNum ?: 0);
        $outShipNum = DB::table('os_fba_delivery_detail as odd')
            ->join('os_fba_delivery as od', 'odd.delivery_no', '=', 'od.delivery_no')
            ->where('od.country_id',$countryId)
            ->where('odd.psku_id',$pSkuId)
            ->where('od.status','>',2)
            ->where('od.created_at','>=',$date)
            ->sum('odd.ship_num');
        $outApplyNum = DB::table('os_fba_delivery_detail as odd')
            ->join('os_fba_delivery as od', 'odd.delivery_no', '=', 'od.delivery_no')
            ->where('od.country_id',$countryId)
            ->where('odd.psku_id',$pSkuId)
            ->where('od.status','<=',2)
            ->where('od.created_at','>=',$date)
            ->sum('odd.apply_num');
        $outReceivedNum = DB::table('os_fba_delivery_detail as odd')
            ->join('os_fba_delivery as od', 'odd.delivery_no', '=', 'od.delivery_no')
            ->where('od.country_id',$countryId)
            ->where('odd.psku_id',$pSkuId)
            ->where('od.created_at','>=',$date)
            ->sum('odd.received_num');
        $outNum = ($outShipNum ?: 0) + ($outApplyNum ?: 0) - ($outReceivedNum ?: 0);
        return ($num < 0 ? 0 : $num) + ($outNum < 0 ? 0 : $outNum);
    }
}
